// src/adapters/database_adapter.rs

//! 数据库适配器
//! 支持关系型数据库和NoSQL数据库的适配

use crate::interfaces::{
    DataSourceAdapter, DataSourceConnection, ConnectionStatus, DataSourceSchema,
    SchemaExtractionResult, DataQuery, QueryResult, TestResult,
};
use crate::models::{DataSourceType, FieldDefinition, Relationship, Constraint};
use async_trait::async_trait;
use sqlx::{Row, Pool, Postgres, MySql, Sqlite, postgres::PgPoolOptions};
use serde::{Deserialize, Serialize};
use std::collections::HashMap;
use anyhow::{anyhow, Result as AnyhowResult};
use log::{debug, info, warn, error};
use chrono::Utc;

/// 数据库适配器 - 支持多种数据库类型
pub struct DatabaseAdapter {
    /// 数据库连接池
    pool: Option<Pool<Postgres>>,
    /// 数据库配置
    config: DatabaseConfig,
    /// 数据库类型
    db_type: DatabaseType,
    /// 连接状态
    is_connected: bool,
}

/// 数据库配置
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct DatabaseConfig {
    /// 数据库连接URL
    pub connection_url: String,
    /// 最大连接数
    pub max_connections: u32,
    /// 连接超时时间（秒）
    pub connect_timeout_secs: u64,
    /// 查询超时时间（秒）
    pub query_timeout_secs: u64,
    /// 是否启用连接池
    pub enable_pool: bool,
    /// 连接池大小
    pub pool_size: u32,
    /// 数据库模式
    pub schema: Option<String>,
}

/// 支持的数据库类型
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
pub enum DatabaseType {
    PostgreSQL,
    MySQL,
    SQLite,
    Oracle,
    SQLServer,
}

/// 数据库表结构信息
#[derive(Debug, Clone, Serialize)]
pub struct TableInfo {
    pub table_name: String,
    pub table_schema: String,
    pub table_type: String,
    pub columns: Vec<ColumnInfo>,
    pub indexes: Vec<IndexInfo>,
    pub foreign_keys: Vec<ForeignKeyInfo>,
}

/// 列信息
#[derive(Debug, Clone, Serialize)]
pub struct ColumnInfo {
    pub column_name: String,
    pub data_type: String,
    pub is_nullable: bool,
    pub is_primary_key: bool,
    pub default_value: Option<String>,
    pub character_maximum_length: Option<i32>,
    pub numeric_precision: Option<i32>,
    pub numeric_scale: Option<i32>,
}

/// 索引信息
#[derive(Debug, Clone, Serialize)]
pub struct IndexInfo {
    pub index_name: String,
    pub column_names: Vec<String>,
    pub is_unique: bool,
    pub index_type: String,
}

/// 外键信息
#[derive(Debug, Clone, Serialize)]
pub struct ForeignKeyInfo {
    pub constraint_name: String,
    pub column_name: String,
    pub foreign_table_name: String,
    pub foreign_column_name: String,
}

impl DatabaseAdapter {
    /// 创建新的数据库适配器实例
    pub fn new(config: DatabaseConfig) -> Result<Self> {
        let db_type = Self::detect_database_type(&config.connection_url)?;
        
        Ok(Self {
            pool: None,
            config,
            db_type,
            is_connected: false,
        })
    }
    
    /// 检测数据库类型
    fn detect_database_type(connection_url: &str) -> Result<DatabaseType> {
        if connection_url.starts_with("postgresql://") || connection_url.starts_with("postgres://") {
            Ok(DatabaseType::PostgreSQL)
        } else if connection_url.starts_with("mysql://") {
            Ok(DatabaseType::MySQL)
        } else if connection_url.starts_with("sqlite://") {
            Ok(DatabaseType::SQLite)
        } else if connection_url.starts_with("oracle://") {
            Ok(DatabaseType::Oracle)
        } else if connection_url.starts_with("sqlserver://") || connection_url.starts_with("mssql://") {
            Ok(DatabaseType::SQLServer)
        } else {
            Err(anyhow!("不支持的数据库连接URL格式: {}", connection_url))
        }
    }
    
    /// 获取数据库表列表
    async fn get_tables(&self) -> Result<Vec<TableInfo>> {
        let query = match self.db_type {
            DatabaseType::PostgreSQL => "
                SELECT table_name, table_schema, table_type 
                FROM information_schema.tables 
                WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
                ORDER BY table_schema, table_name
            ",
            DatabaseType::MySQL => "
                SELECT table_name, table_schema, table_type 
                FROM information_schema.tables 
                WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
                ORDER BY table_schema, table_name
            ",
            DatabaseType::SQLite => "
                SELECT name as table_name, 'main' as table_schema, 'BASE TABLE' as table_type
                FROM sqlite_master 
                WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
                ORDER BY name
            ",
            _ => return Err(anyhow!("暂不支持的数据库类型: {:?}", self.db_type)),
        };
        
        let rows = sqlx::query(query)
            .fetch_all(self.pool.as_ref().unwrap())
            .await?;
            
        let mut tables = Vec::new();
        
        for row in rows {
            let table_name: String = row.get("table_name");
            let table_schema: String = row.get("table_schema");
            let table_type: String = row.get("table_type");
            
            let columns = self.get_table_columns(&table_schema, &table_name).await?;
            let indexes = self.get_table_indexes(&table_schema, &table_name).await?;
            let foreign_keys = self.get_foreign_keys(&table_schema, &table_name).await?;
            
            tables.push(TableInfo {
                table_name,
                table_schema,
                table_type,
                columns,
                indexes,
                foreign_keys,
            });
        }
        
        Ok(tables)
    }
    
    /// 获取表列信息
    async fn get_table_columns(&self, schema: &str, table: &str) -> Result<Vec<ColumnInfo>> {
        let query = match self.db_type {
            DatabaseType::PostgreSQL => "
                SELECT 
                    column_name, data_type, is_nullable,
                    column_default as default_value,
                    character_maximum_length,
                    numeric_precision, numeric_scale,
                    EXISTS (
                        SELECT 1 FROM information_schema.key_column_usage kcu
                        WHERE kcu.table_schema = c.table_schema 
                        AND kcu.table_name = c.table_name 
                        AND kcu.column_name = c.column_name
                        AND EXISTS (
                            SELECT 1 FROM information_schema.table_constraints tc
                            WHERE tc.constraint_name = kcu.constraint_name
                            AND tc.constraint_type = 'PRIMARY KEY'
                        )
                    ) as is_primary_key
                FROM information_schema.columns c
                WHERE table_schema = $1 AND table_name = $2
                ORDER BY ordinal_position
            ",
            DatabaseType::MySQL => "
                SELECT 
                    column_name, data_type, is_nullable,
                    column_default as default_value,
                    character_maximum_length,
                    numeric_precision, numeric_scale,
                    column_key = 'PRI' as is_primary_key
                FROM information_schema.columns
                WHERE table_schema = ? AND table_name = ?
                ORDER BY ordinal_position
            ",
            _ => return Err(anyhow!("暂不支持的数据库类型")),
        };
        
        let rows = sqlx::query(query)
            .bind(schema)
            .bind(table)
            .fetch_all(self.pool.as_ref().unwrap())
            .await?;
            
        let mut columns = Vec::new();
        
        for row in rows {
            columns.push(ColumnInfo {
                column_name: row.get("column_name"),
                data_type: row.get("data_type"),
                is_nullable: row.get::<String, _>("is_nullable") == "YES",
                is_primary_key: row.get("is_primary_key"),
                default_value: row.get("default_value"),
                character_maximum_length: row.get("character_maximum_length"),
                numeric_precision: row.get("numeric_precision"),
                numeric_scale: row.get("numeric_scale"),
            });
        }
        
        Ok(columns)
    }
    
    /// 获取表索引信息
    async fn get_table_indexes(&self, schema: &str, table: &str) -> Result<Vec<IndexInfo>> {
        // 简化实现，实际应根据不同数据库调整
        let query = match self.db_type {
            DatabaseType::PostgreSQL => "
                SELECT 
                    indexname as index_name,
                    indisunique as is_unique,
                    indexdef as index_definition
                FROM pg_indexes 
                WHERE schemaname = $1 AND tablename = $2
            ",
            _ => return Ok(Vec::new()), // 简化处理
        };
        
        let rows = sqlx::query(query)
            .bind(schema)
            .bind(table)
            .fetch_all(self.pool.as_ref().unwrap())
            .await?;
            
        let mut indexes = Vec::new();
        
        for row in rows {
            indexes.push(IndexInfo {
                index_name: row.get("index_name"),
                column_names: Vec::new(), // 需要解析索引定义获取列名
                is_unique: row.get("is_unique"),
                index_type: "BTREE".to_string(), // 简化处理
            });
        }
        
        Ok(indexes)
    }
    
    /// 获取外键信息
    async fn get_foreign_keys(&self, schema: &str, table: &str) -> Result<Vec<ForeignKeyInfo>> {
        // 简化实现
        Ok(Vec::new())
    }
    
    /// 将数据库类型映射为OPC UA数据类型
    fn map_data_type_to_opcua(db_type: &str) -> DataType {
        match db_type.to_lowercase().as_str() {
            "integer" | "int" | "bigint" | "smallint" | "serial" => DataType::Int32,
            "varchar" | "text" | "char" | "character varying" => DataType::String,
            "boolean" | "bool" => DataType::Boolean,
            "real" | "double precision" | "float" | "float8" => DataType::Double,
            "numeric" | "decimal" => DataType::Decimal,
            "timestamp" | "timestamptz" | "datetime" => DataType::DateTime,
            "date" => DataType::Date,
            "time" => DataType::Time,
            "bytea" | "blob" => DataType::ByteString,
            _ => DataType::String, // 默认处理
        }
    }
    
    /// 从表信息生成实体定义
    fn table_to_entity_definition(&self, table: &TableInfo) -> EntityDefinition {
        let properties: Vec<PropertyDefinition> = table.columns.iter()
            .map(|col| {
                PropertyDefinition {
                    name: col.column_name.clone(),
                    data_type: Self::map_data_type_to_opcua(&col.data_type),
                    is_required: !col.is_nullable,
                    default_value: col.default_value.clone()
                        .map(|v| serde_json::Value::String(v)),
                    description: Some(format!("数据类型: {}, 主键: {}", 
                        col.data_type, col.is_primary_key)),
                }
            })
            .collect();
            
        EntityDefinition {
            name: table.table_name.clone(),
            description: Some(format!("数据库表: {}.{}", 
                table.table_schema, table.table_name)),
            properties,
            tags: HashMap::from([
                ("schema".to_string(), table.table_schema.clone()),
                ("table_type".to_string(), table.table_type.clone()),
                ("database_type".to_string(), format!("{:?}", self.db_type)),
            ]),
        }
    }
    
    /// 从外键生成关系定义
    fn foreign_keys_to_relationships(&self, tables: &[TableInfo]) -> Vec<Relationship> {
        let mut relationships = Vec::new();
        
        for table in tables {
            for fk in &table.foreign_keys {
                relationships.push(Relationship {
                    source: table.table_name.clone(),
                    target: fk.foreign_table_name.clone(),
                    relationship_type: "foreign_key".to_string(),
                    properties: HashMap::from([
                        ("constraint_name".to_string(), fk.constraint_name.clone().into()),
                        ("column_name".to_string(), fk.column_name.clone().into()),
                        ("foreign_column_name".to_string(), fk.foreign_column_name.clone().into()),
                    ]),
                });
            }
        }
        
        relationships
    }
}

#[async_trait]
impl DataSourceAdapter for DatabaseAdapter {
    /// 连接数据库
    async fn connect(&self, _data_source_type: &str, connection_string: &str) 
        -> Result<Box<dyn DataSourceConnection>> 
    {
        info!("正在连接数据库: {}", connection_string);
        
        let pool = PgPoolOptions::new()
            .max_connections(self.config.max_connections)
            .connect_timeout(std::time::Duration::from_secs(self.config.connect_timeout_secs))
            .connect(connection_string)
            .await
            .map_err(|e| anyhow!("数据库连接失败: {}", e))?;
            
        let connection = DatabaseConnection {
            pool: Some(pool),
            is_connected: true,
            config: self.config.clone(),
        };
        
        info!("数据库连接成功");
        Ok(Box::new(connection))
    }

    /// 提取数据库Schema
    async fn extract_schema(&self, _connection: &dyn DataSourceConnection) -> AnyhowResult<DataSourceSchema> {
        if !self.is_connected {
            return Err(anyhow!("数据库未连接"));
        }
        
        info!("开始提取数据库Schema...");
        
        let tables = self.get_tables().await?;
        info!("发现 {} 个表", tables.len());
        
        let entities: Vec<EntityDefinition> = tables.iter()
            .map(|table| self.table_to_entity_definition(table))
            .collect();
            
        let relationships = self.foreign_keys_to_relationships(&tables);
        info!("发现 {} 个关系", relationships.len());
        
        // 生成方法定义（存储过程等）
        let methods = self.extract_database_methods().await.unwrap_or_else(|_| Vec::new());
        
        let schema = DataSourceSchema {
            entities,
            relationships,
            methods,
            metadata: HashMap::from([
                ("database_type".to_string(), format!("{:?}", self.db_type).into()),
                ("table_count".to_string(), tables.len().into()),
                ("extraction_time".to_string(), Utc::now().to_rfc3339().into()),
            ]),
        };
        
        info!("数据库Schema提取完成");
        Ok(schema)
    }

    /// 测试数据库连接
    async fn test_connection(&self, _connection: &dyn DataSourceConnection) -> AnyhowResult<bool> {
        if let Some(pool) = &self.pool {
            match sqlx::query("SELECT 1").execute(pool).await {
                Ok(_) => Ok(true),
                Err(e) => {
                    error!("数据库连接测试失败: {}", e);
                    Ok(false)
                }
            }
        } else {
            Ok(false)
        }
    }

    /// 获取支持的数据库类型
    fn get_supported_data_sources(&self) -> Vec<String> {
        vec![
            "PostgreSQL".to_string(),
            "MySQL".to_string(),
            "SQLite".to_string(),
            "Oracle".to_string(),
            "SQLServer".to_string(),
        ]
    }
}

/// 数据库连接实现
pub struct DatabaseConnection {
    pool: Option<Pool<Postgres>>,
    is_connected: bool,
    config: DatabaseConfig,
}

impl DatabaseConnection {
    /// 执行SQL查询
    pub async fn execute_query(&self, query: &str) -> Result<Vec<serde_json::Value>> {
        let rows = sqlx::query(query)
            .fetch_all(self.pool.as_ref().unwrap())
            .await?;
            
        let mut results = Vec::new();
        
        for row in rows {
            let mut record = serde_json::Map::new();
            for (i, column) in row.columns().iter().enumerate() {
                let value: serde_json::Value = match row.try_get(i) {
                    Ok(v) => v,
                    Err(_) => serde_json::Value::Null,
                };
                record.insert(column.name().to_string(), value);
            }
            results.push(serde_json::Value::Object(record));
        }
        
        Ok(results)
    }
    
    /// 获取数据库元数据
    pub async fn get_database_metadata(&self) -> Result<serde_json::Value> {
        let version_query = "SELECT version() as db_version";
        let version_row = sqlx::query(version_query)
            .fetch_one(self.pool.as_ref().unwrap())
            .await?;
            
        let db_version: String = version_row.get("db_version");
        
        let metadata = serde_json::json!({
            "database_version": db_version,
            "connection_url": self.config.connection_url,
            "max_connections": self.config.max_connections,
            "connected_at": Utc::now().to_rfc3339(),
        });
        
        Ok(metadata)
    }
}

#[async_trait]
impl DataSourceConnection for DatabaseConnection {
    async fn open(&mut self) -> Result<()> {
        if self.is_connected {
            return Ok(());
        }
        
        let pool = PgPoolOptions::new()
            .max_connections(self.config.max_connections)
            .connect(&self.config.connection_url)
            .await?;
            
        self.pool = Some(pool);
        self.is_connected = true;
        
        Ok(())
    }
    
    async fn close(&mut self) -> Result<()> {
        if let Some(pool) = self.pool.take() {
            pool.close().await;
        }
        self.is_connected = false;
        info!("数据库连接已关闭");
        Ok(())
    }
    
    fn is_connected(&self) -> bool {
        self.is_connected
    }
    
    async fn query_data(&self, query: &str) -> Result<Vec<serde_json::Value>> {
        self.execute_query(query).await
    }
}

/// 数据库适配器工厂
pub struct DatabaseAdapterFactory;

impl DatabaseAdapterFactory {
    /// 根据配置创建数据库适配器
    pub fn create_adapter(config: DatabaseConfig) -> Result<DatabaseAdapter> {
        DatabaseAdapter::new(config)
    }
    
    /// 从连接字符串创建适配器
    pub fn create_from_connection_string(connection_string: &str) -> Result<DatabaseAdapter> {
        let config = DatabaseConfig {
            connection_url: connection_string.to_string(),
            max_connections: 10,
            connect_timeout_secs: 30,
            query_timeout_secs: 30,
            enable_pool: true,
            pool_size: 10,
            schema: None,
        };
        
        Self::create_adapter(config)
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    
    #[tokio::test]
    async fn test_database_type_detection() {
        let postgres_url = "postgresql://user:pass@localhost/db";
        let mysql_url = "mysql://user:pass@localhost/db";
        let sqlite_url = "sqlite:///path/to/db.sqlite";
        
        assert_eq!(
            DatabaseAdapter::detect_database_type(postgres_url).unwrap(),
            DatabaseType::PostgreSQL
        );
        assert_eq!(
            DatabaseAdapter::detect_database_type(mysql_url).unwrap(),
            DatabaseType::MySQL
        );
        assert_eq!(
            DatabaseAdapter::detect_database_type(sqlite_url).unwrap(),
            DatabaseType::SQLite
        );
    }
    
    #[test]
    fn test_data_type_mapping() {
        assert_eq!(
            DatabaseAdapter::map_data_type_to_opcua("integer"),
            DataType::Int32
        );
        assert_eq!(
            DatabaseAdapter::map_data_type_to_opcua("varchar"),
            DataType::String
        );
        assert_eq!(
            DatabaseAdapter::map_data_type_to_opcua("boolean"),
            DataType::Boolean
        );
        assert_eq!(
            DatabaseAdapter::map_data_type_to_opcua("timestamp"),
            DataType::DateTime
        );
    }
}

